package com.wenjie.databseDiff.handle.DatasourceSynchronizer.imp;

import cn.hutool.core.util.BooleanUtil;
import com.wenjie.databseDiff.enums.Drivers;
import com.wenjie.databseDiff.handle.DatasourceSynchronizer.AbstractDatasourceSynchronizer;
import com.wenjie.original.entity.SysDiffColumn;
import com.wenjie.original.entity.SysDiffTable;
import lombok.extern.slf4j.Slf4j;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * H2数据源同步
 *
 * @author wxg
 */
@Slf4j
public class OracleDatasourceSynchronizer extends AbstractDatasourceSynchronizer {

    private static final String SQL_QUERY_TABLE = "select uat.*,utc.COMMENTS from USER_ALL_TABLES uat left join USER_TAB_COMMENTS utc on uat.TABLE_NAME=utc.TABLE_NAME ";
    private static final String SQL_QUERY_COLUMN = "select utc.*,ucc.COMMENTS from USER_TAB_COLUMNS utc left join USER_COL_COMMENTS ucc on utc.TABLE_NAME=ucc.TABLE_NAME and utc.COLUMN_NAME=ucc.COLUMN_NAME where utc.TABLE_NAME=? ";
    private static final String SQL_QUERY_INDEX = "select ui.TABLE_NAME,ui.INDEX_NAME,listagg(uic.COLUMN_NAME,',') as COLUMNS from USER_INDEXES ui left join USER_IND_COLUMNS uic on ui.TABLE_NAME=uic.TABLE_NAME and ui.INDEX_NAME=uic.INDEX_NAME group by ui.TABLE_NAME,ui.INDEX_NAME";

    public OracleDatasourceSynchronizer(String url, String user, String password) {
        super(url, user, password);
    }


    @Override
    public List<SysDiffTable> getTables(Connection connection, String database) {
        List<SysDiffTable> allTables = getAllTables(connection, database);
        log.info("Find {} tables", allTables.size());
        Map<String, List<String>> allTableIndexes = getAllTableIndexes(connection, database);
        log.info("Find {} indexes", allTableIndexes.size());
        for (SysDiffTable SysDiffTable : allTables) {
            List<String> indexes = allTableIndexes.get(SysDiffTable.getTableName());
            if (indexes != null) {
                SysDiffTable.setTableIndex(String.join("; ", indexes));
            }
        }
        return allTables;
    }

    private List<SysDiffTable> getAllTables(Connection connection, String database) {
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_TABLE);
            ResultSet resultSet = statement.executeQuery();
            List<SysDiffTable> tableInfos = new ArrayList<>();
            while (resultSet.next()) {
                SysDiffTable SysDiffTable = new SysDiffTable();
                SysDiffTable.setTableName(resultSet.getString("TABLE_NAME"));
                SysDiffTable.setTableType(null);
                SysDiffTable.setTableRows(null);
                SysDiffTable.setTableComment(resultSet.getString("COMMENTS"));
                SysDiffTable.setTableCollation(null);
                SysDiffTable.setCreateTime(null);
                SysDiffTable.setUpdateTime(null);
                SysDiffTable.setTableEngine(null);
                tableInfos.add(SysDiffTable);
            }
            return tableInfos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    private Map<String, List<String>> getAllTableIndexes(Connection connection, String database) {
        Map<String, List<String>> indexMap = new HashMap<>();
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_INDEX);
            ResultSet indexResultSet = statement.executeQuery();
            while (indexResultSet.next()) {
                String tableName = indexResultSet.getString("TABLE_NAME");
                String indexName = indexResultSet.getString("INDEX_NAME");
                String indexColumn = indexResultSet.getString("COLUMNS");
                List<String> list = indexMap.computeIfAbsent(tableName, k -> new ArrayList<>());
                list.add(indexName + "(" + indexColumn + ")");
            }
            return indexMap;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public List<SysDiffColumn> getColumns(Connection connection, String database, String tableName) {
        List<SysDiffColumn> columnInfos = new ArrayList<>();
        PreparedStatement statement;
        try {
            statement = connection.prepareStatement(SQL_QUERY_COLUMN);

            statement.setString(1, tableName);
            ResultSet resultSet = statement.executeQuery();

            while (resultSet.next()) {
                SysDiffColumn SysDiffColumn = new SysDiffColumn();
                SysDiffColumn.setColumnName(resultSet.getString("COLUMN_NAME"));
                SysDiffColumn.setColumnSort(resultSet.getInt("COLUMN_ID"));
                SysDiffColumn.setColumnDefault(resultSet.getObject("DATA_DEFAULT"));
                SysDiffColumn.setColumnDataType(resultSet.getString("DATA_TYPE"));
                //TODO 长度
                SysDiffColumn.setColumnType(resultSet.getString("DATA_TYPE"));
                SysDiffColumn.setColumnIndex(null);
                SysDiffColumn.setColumnNull(BooleanUtil.toBoolean(resultSet.getString("NULLABLE")));
                SysDiffColumn.setColumnComment(resultSet.getString("COMMENTS"));
                SysDiffColumn.setTableName(resultSet.getString("TABLE_NAME"));
                columnInfos.add(SysDiffColumn);
            }
            return columnInfos;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public String getDriverFile() {
        return Drivers.ORACLE.getDriver();
    }

    @Override
    public String getDriverClassName() {
        return Drivers.ORACLE.getDriverClassName();
    }

}
